﻿using System;
using System.Collections.Generic;
using BalanceManagerApp.Model;

namespace BalanceManagerApp.DataAccess
{
    public class ItemRepository : IItemRepository
    {
        public void Save(Item item)
        {
            if (!IsCategoryExists(item.Category))
                InsertNewCategory(item.Category);

            if (IsItemExists(item))
                UpdateItem(item);
            else
                InsertNewItem(item);
        }

        public void Delete(Item item)
        {
            var query = @"DELETE Item WHERE Id = @id";
            using (var queryHelper = new SqlCeDbQueryHelper())
            {
                queryHelper.ExecuteNonQuery(query, new Dictionary<string, object> { { "@id", item.Id } });
            }
        }

        public List<Item> SelectItems()
        {
            var query = @"SELECT i.Id, i.Name, c.Name AS Category FROM Item AS i INNER JOIN Category AS c ON i.Category = c.Id";
            var itemsList = new List<Item>();

            using (var queryHelper = new SqlCeDbQueryHelper())
            using (var reader = queryHelper.ExecuteReader(query, null))
            {
                while (reader.Read())
                {
                    var item = new Item
                        {
                            Id = (int)reader["Id"],
                            Name = (string)reader["Name"],
                            Category = (string)reader["Category"]
                        };
                    itemsList.Add(item);
                }
                return itemsList;
            }
        }

        #region Private helpers
        static bool IsItemExists(Item item)
        {
            var query = @"SELECT COUNT(*) FROM Item WHERE Id = @id";
            using (var queryHelper = new SqlCeDbQueryHelper())
            {
                var rowCount = (int)queryHelper.ExecuteScalar(query, new Dictionary<string, object> { { "@id", item.Id } });
                return (rowCount != 0);
            }
        }

        static int GetCategoryId(Item item)
        {
            var query = @"SELECT Id FROM Category WHERE Name = @name";
            using (var queryHelper = new SqlCeDbQueryHelper())
            {
                var categoryId = (int)queryHelper.ExecuteScalar(query, new Dictionary<string, object> { { "@name", item.Category } });
                return categoryId;
            }
        }

        static void InsertNewItem(Item item)
        {
            var query = @"INSERT INTO Item(Name, Category) VALUES (@name, @category)";
            using (var queryHelper = new SqlCeDbQueryHelper())
            {
                queryHelper.ExecuteNonQuery(query, new Dictionary<string, object> { { "@name", item.Name }, {"@category", GetCategoryId(item)} });
                var itemId = Convert.ToInt32(queryHelper.ExecuteScalar(@"SELECT @@IDENTITY", null));
                item.Id = itemId;
            }
        }

        static void UpdateItem(Item item)
        {
            var query = @"UPDATE Item SET Name = @name, Category = @category WHERE Id = @id";
            var parameters = new Dictionary<string, object>();
            parameters.Add("@id", item.Id);
            parameters.Add("@name", item.Name);
            parameters.Add("@category", GetCategoryId(item));

            using (var queryHelper = new SqlCeDbQueryHelper())
            {
                queryHelper.ExecuteScalar(query, parameters);
            }
        }

        static bool IsCategoryExists(string name)
        {
            var query = @"SELECT COUNT(*) FROM Category WHERE Name = @categoryName";
            using (var queryHelper = new SqlCeDbQueryHelper())
            {
                var rowCount = (int)queryHelper.ExecuteScalar(query, new Dictionary<string, object> { { "@categoryName", name } });
                return (rowCount != 0);
            } 
        }

        void InsertNewCategory(string name)
        {
            var query = @"INSERT INTO Category(Name) VALUES (@categoryName)";
            using (var queryHelper = new SqlCeDbQueryHelper())
            {
                queryHelper.ExecuteNonQuery(query, new Dictionary<string, object> { { "@categoryName", name } });
            }
        }
        #endregion
    }
}
